/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ZeroHora.DAO;

/**
 *
 * @author Gledson
 */

import br.com.cadastros.bean.Cliente;
import java.util.List;
import java.util.ArrayList;

public class clienteDAO extends DAO {    
    public void cadastrar(Cliente c) throws Exception{
        openDatabase();
            String sql = "INSERT INTO cliente (nome, endereco, numero, telefone, cidade, email) VALUES (?,?,?,?,?,?)";
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, c.getNome());
            pstmt.setString(2, c.getEndereco());
            pstmt.setString(3, c.getNumero());
            pstmt.setString(4, c.getTelefone());
            pstmt.setString(5, c.getCidade());            
            pstmt.setString(6, c.getEmail());
            pstmt.execute();
        closeDatabase();
    }
 public Cliente busca(Integer id) throws Exception{
        openDatabase();
        String sql = "select * from cliente where idCliente=?";
        pstmt = con.prepareStatement(sql);
        pstmt.setInt(1, id);
        rs = pstmt.executeQuery();
        Cliente c = new Cliente();
        if(rs.next()){
            c.setIdCliente(rs.getInt("idCliente"));
            c.setNome(rs.getString("nome"));
            c.setEndereco(rs.getString("endereco"));
            c.setNumero(rs.getString("numero"));
             c.setTelefone(rs.getString("telefone"));
            c.setCidade(rs.getString("cidade"));           
            c.setEmail(rs.getString("email"));
        }
        return c;
    }
 
 public Cliente buscaNome(String nm) throws Exception{
        openDatabase();
        String sql = "select nome, endereco, telefone, email from cliente  where nome like '%?%'";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, nm);
        rs = pstmt.executeQuery();
        Cliente c = new Cliente();
        if(rs.next()){
            c.setNome(rs.getString("nome"));
            c.setEndereco(rs.getString("endereco"));
             c.setTelefone(rs.getString("telefone"));           
            c.setEmail(rs.getString("email"));
        }
        return c;
    }
    
    public void delete(Cliente c)throws Exception{
        openDatabase();
        String sql = "DELETE from cliente WHERE idCliente = ?";
        pstmt = con.prepareStatement(sql);//Não é obrigatório, ele prepara o sql
        pstmt.setInt(1, c.getIdCliente());
        pstmt.execute();
    }
    
    public List<Cliente> listaCliente() throws Exception{
        openDatabase();
        String sql = "SELECT * FROM cliente";
        pstmt = con.prepareStatement(sql);
        rs = pstmt.executeQuery();
        Cliente c = null;
       List<Cliente> listaCliente = new ArrayList<Cliente>();
       while(rs.next()){
           c = new Cliente();
                    c.setIdCliente(rs.getInt("idCliente"));
                    c.setNome(rs.getString("nome"));
                    c.setEndereco(rs.getString("endereco"));
                    c.setNumero(rs.getString("numero"));
                    c.setCidade(rs.getString("telefone"));
                    c.setEmail(rs.getString("cidade"));
                    c.setTelefone(rs.getString("email"));
                    
                   listaCliente.add(c);
       }           
       return listaCliente;        
    }
    
    public void update(Cliente c)throws Exception{
        openDatabase();
        String sql = "UPDATE cliente SET nome=?, endereco=? , numero=?, telefone=?, cidade=?, email=?" + 
                "WHERE idCliente=?";
       pstmt = con.prepareStatement(sql);
       pstmt.setString(1, c.getNome());
       pstmt.setString(2, c.getEndereco());        
       pstmt.setString(3, c.getNumero());        
       pstmt.setString(4, c.getTelefone());        
       pstmt.setString(5, c.getCidade());        
       pstmt.setString(6, c.getEmail());        
       pstmt.setInt(7, c.getIdCliente());        
       pstmt.executeUpdate();
        
    }
}
